package com.union.core.doc.dao.impl;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.xml.ws.Response;

import org.apache.log4j.Logger;

import com.hnjowe.util.ComUtil;
import com.hnjowe.util.DateUtil;
import com.hnjowe.webdb.DBOperation;
import com.mysql.jdbc.StringUtils;
import com.union.core.IConstant;
import com.union.core.doc.dao.OfficialDocDao;
import com.union.core.doc.model.DocAttachment;
import com.union.core.doc.model.DocTemplate;
import com.union.core.doc.model.OfficialDoc;
import com.union.core.doc.model.OfficialDocOrg;
import com.union.core.doc.model.SealSignature;
import com.union.core.doc.pojo.OfficialDocOrgPojo;
import com.union.core.doc.pojo.OfficialDocPojo;

public class OfficialDocDaoImpl implements OfficialDocDao{
	private DBOperation opera;
	private OfficialDoc officialDoc;
	private static Logger log = Logger.getLogger(OfficialDocDaoImpl.class.getName());


	public int queryCount(OfficialDocPojo officialDocPojo,int creatId) {
		int totalSize = 0;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql= "select count(1) as totalSize "
						+ " from doc t "
						+ " where t.effect_flag=1 ";
			if(creatId != 0){
				sql += "  and create_userid = ? ";
			}
			if(officialDocPojo.getStartDate() != null){
				sql += " and t.create_time >= ? ";
			}
			if(officialDocPojo.getEndDate() != null){
				sql += " and t.create_time <= ?";
			}
			if(!StringUtils.isNullOrEmpty(officialDocPojo.getCondTxt())){
				if(IConstant.DOC_COND_TYPE_TIT.equals(officialDocPojo.getCondType())){
					sql += " and t.title like ? ";
				}else if(IConstant.DOC_COND_TYPE_NO.equals(officialDocPojo.getCondType())){
					sql += " and t.doc_no like ? ";
				}
			}
			
			opera.prepStatment(sql);
			int i = 1;
			if(creatId != 0){
				opera.setLong(1, creatId);
				++i;
			}
			if(officialDocPojo.getStartDate() != null){
				opera.setString(i, DateUtil.dateToStringFour(officialDocPojo.getStartDate()));
				++i;
			}
			if(officialDocPojo.getEndDate() != null){
				opera.setString(i, DateUtil.dateToStringFour(DateUtil.getEndDay(officialDocPojo.getEndDate())));
				++i;
			}
			if(!StringUtils.isNullOrEmpty(officialDocPojo.getCondTxt())){
				if(IConstant.DOC_COND_TYPE_TIT.equals(officialDocPojo.getCondType())){
					opera.setString(i, "%"+officialDocPojo.getCondTxt()+"%");
					++i;
				}else if(IConstant.DOC_COND_TYPE_NO.equals(officialDocPojo.getCondType())){
					opera.setString(i, "%"+officialDocPojo.getCondTxt()+"%");
					++i;
				}
			}
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				totalSize = res.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return totalSize;
	}
	
	public List<OfficialDoc> query(OfficialDocPojo officialDocPojo, int start,
			int pageSize ,int creatId) {
		List<OfficialDoc> officialDocList = new ArrayList<OfficialDoc>();
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select t.* " + " from doc t " + " where t.effect_flag=1 ";
			if(creatId != 0){
				sql += "  and create_userid = ? ";
			}
			if(officialDocPojo.getStartDate() != null){
				sql += " and t.create_time >= ? ";
			}
			if(officialDocPojo.getEndDate() != null){
				sql += " and t.create_time <= ?";
			}
			
			if(!StringUtils.isNullOrEmpty(officialDocPojo.getCondTxt())){
				if(IConstant.DOC_COND_TYPE_TIT.equals(officialDocPojo.getCondType())){
					sql += " and t.title like ? ";
				}else if(IConstant.DOC_COND_TYPE_NO.equals(officialDocPojo.getCondType())){
					sql += " and t.doc_no like ? ";
				}
			}
			sql += " order by id desc LIMIT ?,?";
			
			opera.prepStatment(sql);
			
			
			int i = 1;
			if(creatId != 0){
				opera.setLong(1, creatId);
				++i;
			}
			if(officialDocPojo.getStartDate() != null){
				opera.setString(i, DateUtil.dateToStringFour(officialDocPojo.getStartDate()));
				++i;
			}
			if(officialDocPojo.getEndDate() != null){
				opera.setString(i, DateUtil.dateToStringFour(DateUtil.getEndDay(officialDocPojo.getEndDate())));
				++i;
			}
			if(!StringUtils.isNullOrEmpty(officialDocPojo.getCondTxt())){
				if(IConstant.DOC_COND_TYPE_TIT.equals(officialDocPojo.getCondType())){
					opera.setString(i, "%"+officialDocPojo.getCondTxt()+"%");
					++i;
				}else if(IConstant.DOC_COND_TYPE_NO.equals(officialDocPojo.getCondType())){
					opera.setString(i, "%"+officialDocPojo.getCondTxt()+"%");
					++i;
				}
			}
			
			opera.setInt(i, start);
			++i;
			opera.setInt(i, pageSize);
			++i;
			
			ResultSet res = opera.executeQuery();
			while (null != res && res.next()) {
				officialDoc = new OfficialDoc();
				officialDoc.setId(res.getLong("id"));
				officialDoc.setDoc_no(res.getString("doc_no"));
				officialDoc.setDoc_type(res.getString("doc_type"));
				officialDoc.setBelong_section(res.getString("belong_section"));
				officialDoc.setConfidential_level(res.getString("confidential_level"));
				officialDoc.setTitle(res.getString("title"));
				officialDoc.setContent(res.getString("content"));
				officialDoc.setKey_word(res.getString("key_word"));
				officialDoc.setSend_time(res.getDate("send_time"));
				officialDoc.setEmergency_level(res.getString("emergency_level"));
				officialDoc.setTemplate_id(res.getString("template_id"));
				officialDoc.setSeal_id(res.getLong("seal_id"));
				officialDoc.setReceive_section(res.getString("receive_section"));
				officialDoc.setFlow_flag(res.getString("flow_flag"));
				officialDoc.setEffect_flag(res.getBoolean("effect_flag"));
				officialDoc.setCreate_userid(res.getLong("create_userid"));
				officialDoc.setCreate_time(res.getDate("create_time"));
				officialDoc.setModify_userid(res.getLong("modify_userid"));
				officialDoc.setModify_time(res.getDate("modify_time"));
				officialDoc.setOpt_send_time(res.getDate("opt_send_time"));
				officialDoc.setDoc_file_name(res.getString("doc_file_name"));
				officialDoc.setDoc_file_path(res.getString("doc_file_path"));
				officialDocList.add(officialDoc);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return officialDocList;
	}

	public int ListCount(int id) {
		return 0;
	}

	public OfficialDoc query(long id) {
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			
			String sql = "select * " 
				   + " from doc" 
				   + " where id=?";
			opera.prepStatment(sql);
			opera.setLong(1, id);
			ResultSet res = opera.executeQuery();
			while(null!=res && res.next()){
				officialDoc = new OfficialDoc();
				officialDoc.setId(res.getLong("id"));
				officialDoc.setDoc_no(res.getString("doc_no"));
				officialDoc.setDoc_type(res.getString("doc_type"));
				officialDoc.setBelong_section(res.getString("belong_section"));
				officialDoc.setConfidential_level(res.getString("confidential_level"));
				officialDoc.setTitle(res.getString("title"));
				officialDoc.setContent(res.getString("content"));
				officialDoc.setKey_word(res.getString("key_word"));
				officialDoc.setSend_time(res.getDate("send_time"));
				officialDoc.setEmergency_level(res.getString("emergency_level"));
				officialDoc.setTemplate_id(res.getString("template_id"));
				officialDoc.setSeal_id(res.getLong("seal_id"));
				officialDoc.setReceive_section(res.getString("receive_section"));
				officialDoc.setFlow_flag(res.getString("flow_flag"));
				officialDoc.setEffect_flag(res.getBoolean("effect_flag"));
				officialDoc.setCreate_userid(res.getLong("create_userid"));
				officialDoc.setCreate_time(res.getDate("create_time"));
				officialDoc.setModify_userid(res.getLong("modify_userid"));
				officialDoc.setModify_time(res.getDate("modify_time"));
				officialDoc.setOpt_send_time(res.getDate("opt_send_time"));
				officialDoc.setDoc_file_name(res.getString("doc_file_name"));
				officialDoc.setDoc_file_path(res.getString("doc_file_path"));
				break;
			}
		}catch(Exception e){
			e.printStackTrace();
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return officialDoc;
	}


	public OfficialDoc query(String title) {
		return null;
	}
	public boolean Success(OfficialDoc officialDoc){
			boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "INSERT INTO `doc`(`doc_no`,`doc_type`,`belong_section`,`confidential_level`,`title`,`content`,`key_word`,`send_time`,`emergency_level`," +
			" `template_id`,`seal_id`,`flow_flag`,`effect_flag`,`create_userid`,`create_time`,`modify_userid`,`modify_time`,`receive_section`, `opt_send_time`, `doc_file_name`, `doc_file_path`)" 
		    + " VALUES (?,?,?,?,?,?,?,SYSDATE(),?," +
		    		" ?,?,?,?,?,?,?,?,?,?,?,?)"; 
			opera.prepStatmentRtnKey(sql);
			opera.setString(1,officialDoc.getDoc_no() );
			opera.setString(2, officialDoc.getDoc_type());
			opera.setString(3, officialDoc.getBelong_section());
			opera.setString(4, officialDoc.getConfidential_level());
			opera.setString(5, officialDoc.getTitle());
			opera.setString(6, officialDoc.getContent());
			opera.setString(7, officialDoc.getKey_word());
			//opera.setString(8, DateUtil.dateToStringFour(officialDoc.getSend_time()));
			opera.setString(8, officialDoc.getEmergency_level());
			opera.setString(9, officialDoc.getTemplate_id());
			opera.setLong(10, officialDoc.getSeal_id());
			opera.setString(11, officialDoc.getFlow_flag());
			opera.setInt(12, ComUtil.boolToInt(officialDoc.isEffect_flag()));
			opera.setLong(13, officialDoc.getCreate_userid());
			opera.setString(14, DateUtil.dateToStringFour(officialDoc.getCreate_time()));
			opera.setLong(15, officialDoc.getModify_userid());
			opera.setString(16, DateUtil.dateToStringFour(officialDoc.getModify_time()));
			opera.setString(17, officialDoc.getReceive_section());
			opera.setString(18, DateUtil.dateToStringFour(officialDoc.getOpt_send_time()));
			opera.setString(19, officialDoc.getDoc_file_name());
			opera.setString(20, officialDoc.getDoc_file_path());
			opera.execute();
			
			ResultSet rsKey = opera.getPres().getGeneratedKeys();
			rsKey.next();
			long key = rsKey.getLong(1);     //得到第一个键值
			rsKey.close();
			officialDoc.setId(key);
			result = opera.transcationCommit();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
			
   }

	public boolean add(OfficialDoc officialDoc) {
    	boolean result = false;
    	try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "INSERT INTO `doc`(`doc_no`,`doc_type`,`belong_section`,`confidential_level`,`title`,`content`,`key_word`,`send_time`,`emergency_level`," +
					" `template_id`,`seal_id`,`flow_flag`,`effect_flag`,`create_userid`,`create_time`,`modify_userid`,`modify_time`,`receive_section`, `opt_send_time`, `doc_file_name`, `doc_file_path`)" 
				    + " VALUES (?,?,?,?,?,?,?,SYSDATE(),?," +
				    		" ?,?,?,?,?,?,?,?,?,?,?,?)"; 
			opera.prepStatmentRtnKey(sql);
			opera.setString(1, officialDoc.getDoc_no());
			opera.setString(2, officialDoc.getDoc_type());
			opera.setString(3, officialDoc.getBelong_section());
			opera.setString(4, officialDoc.getConfidential_level());
			opera.setString(5, officialDoc.getTitle());
			opera.setString(6, officialDoc.getContent());
			opera.setString(7, officialDoc.getKey_word());
			//opera.setString(8, DateUtil.dateToStringFour(officialDoc.getSend_time()));
			opera.setString(8, officialDoc.getEmergency_level());
			opera.setString(9, officialDoc.getTemplate_id());
			opera.setLong(10, officialDoc.getSeal_id());
			opera.setString(11, officialDoc.getFlow_flag());
			opera.setInt(12, ComUtil.boolToInt(officialDoc.isEffect_flag()));
			opera.setLong(13, officialDoc.getCreate_userid());
			opera.setString(14, DateUtil.dateToStringFour(officialDoc.getCreate_time()));
			opera.setDate(15, null);
			opera.setDate(16, null);
			opera.setString(17, officialDoc.getReceive_section());
			opera.setString(18, DateUtil.dateToStringFour(officialDoc.getOpt_send_time()));
			opera.setString(19, officialDoc.getDoc_file_name());
			opera.setString(20, officialDoc.getDoc_file_path());
			opera.execute();
			
			ResultSet rsKey = opera.getPres().getGeneratedKeys();
			rsKey.next();
			long key = rsKey.getLong(1);     //得到第一个键值
			rsKey.close();
			officialDoc.setId(key);
			result = opera.transcationCommit();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	public boolean edit(OfficialDoc officialDoc) {
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "update doc "							
					+ " set `doc_no` = ?,`doc_type` = ?,`belong_section` = ?,`confidential_level` = ?,`title` = ?," +
							"`content` = ?,`key_word` = ?,`send_time` = SYSDATE(),`emergency_level` = ?,`template_id` = ?," +
							"`seal_id` = ?,`flow_flag` = ?,`effect_flag` = ?,`modify_userid` = ?,`modify_time` = ?,`receive_section` = ? ,`opt_send_time` = ?," +
							"`doc_file_name` = ?, `doc_file_path` = ?"
					+ " where id=?";
			opera.prepStatment(sql);
			opera.setString(1, officialDoc.getDoc_no());
			opera.setString(2, officialDoc.getDoc_type());
			opera.setString(3, officialDoc.getBelong_section());
			opera.setString(4, officialDoc.getConfidential_level());
			opera.setString(5, officialDoc.getTitle());
			opera.setString(6, officialDoc.getContent());
			opera.setString(7, officialDoc.getKey_word());
			//opera.setString(8, DateUtil.dateToStringFour(officialDoc.getSend_time()));
			opera.setString(8, officialDoc.getEmergency_level());
			opera.setString(9, officialDoc.getTemplate_id());
			opera.setLong(10, officialDoc.getSeal_id());
			opera.setString(11, officialDoc.getFlow_flag());
			opera.setInt(12, ComUtil.boolToInt(officialDoc.isEffect_flag()));
			opera.setLong(13, officialDoc.getModify_userid());
			opera.setString(14, DateUtil.dateToStringFour(officialDoc.getModify_time()));
			opera.setString(15, officialDoc.getReceive_section());
			opera.setString(16, DateUtil.dateToStringFour(officialDoc.getOpt_send_time()));
			opera.setString(17, officialDoc.getDoc_file_name());
			opera.setString(18, officialDoc.getDoc_file_path());
			opera.setLong(19, officialDoc.getId());

			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	public boolean edit(OfficialDocOrg officialDocOrg) {
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "UPDATE `doc_org` SET `sign_userid` = ?,`sign_time` = ?,`sign_status` = ?,`send_userid` = ? WHERE `id` = ?";
			
			opera.prepStatmentRtnKey(sql);
			opera.setLong(1, officialDocOrg.getSign_userid());
			opera.setString(2, DateUtil.dateToStringFour(officialDocOrg.getSign_time()));
			opera.setString(3, officialDocOrg.getSign_status());
			opera.setLong(4, officialDocOrg.getSend_userid());
			opera.setLong(5, officialDocOrg.getId());
			
			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	public Boolean delete(long id) {
    	boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "delete from doc "
					+ "where id=? ";
			opera.prepStatment(sql);
			opera.setLong(1, id);
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}


	public boolean checkPid(int id) {
		return false;
	}

	public boolean add(OfficialDocOrg officialDocOrg) {
    	boolean result = false;
    	try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "INSERT INTO `union`.`doc_org`(`doc_id`,`org_id`,`send_userid`,`send_time`,`sign_userid`,`sign_time`,`sign_status`)" +
					" VALUES(?,?,?,SYSDATE(),?,?,?)";
			opera.prepStatmentRtnKey(sql);
			opera.setLong(1, officialDocOrg.getDoc_id());
			opera.setLong(2, officialDocOrg.getOrg_id());
			opera.setLong(3, officialDocOrg.getSend_userid());
			//opera.setString(4, Sysdate());
			opera.setString(4, null);
			opera.setString(5, null);
			opera.setString(6, officialDocOrg.getSign_status());
			
			opera.execute();
			ResultSet rsKey = opera.getPres().getGeneratedKeys();
			rsKey.next();
			long key = rsKey.getLong(1);     //得到第一个键值
			rsKey.close();
			officialDocOrg.setId(key);
			result = opera.transcationCommit();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	public int queryDocOrgCount(OfficialDocOrgPojo officialDocOrgPojo) {
		int totalSize = 0;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql= "select count(1) as totalSize "
						+ " from doc_org t,doc a "
						+ " where t.doc_id = a.id and t.sign_status=? and t.org_id=? ";
			if(officialDocOrgPojo.getStartDate() != null){
				sql += " and a.send_time >= ? ";
			}
			if(officialDocOrgPojo.getEndDate() != null){
				sql += " and a.send_time <= ?";
			}
			if(!StringUtils.isNullOrEmpty(officialDocOrgPojo.getCondTxt())){
				if(IConstant.DOC_COND_TYPE_TIT.equals(officialDocOrgPojo.getCondType())){
					sql += " and a.title like ? ";
				}else if(IConstant.DOC_COND_TYPE_NO.equals(officialDocOrgPojo.getCondType())){
					sql += " and a.doc_no like ? ";
				}
			}
			
			opera.prepStatment(sql);
			opera.setString(1, officialDocOrgPojo.getSign_status());
			opera.setLong(2, officialDocOrgPojo.getOrg_id());
			
			int i = 3;
			if(officialDocOrgPojo.getStartDate() != null){
				opera.setString(i, DateUtil.dateToStringFour(officialDocOrgPojo.getStartDate()));
				++i;
			}
			if(officialDocOrgPojo.getEndDate() != null){
				opera.setString(i, DateUtil.dateToStringFour(DateUtil.getEndDay(officialDocOrgPojo.getEndDate())));
				++i;
			}
			if(!StringUtils.isNullOrEmpty(officialDocOrgPojo.getCondTxt())){
				if(IConstant.DOC_COND_TYPE_TIT.equals(officialDocOrgPojo.getCondType())){
					opera.setString(i, "%"+officialDocOrgPojo.getCondTxt()+"%");
					++i;
				}else if(IConstant.DOC_COND_TYPE_NO.equals(officialDocOrgPojo.getCondType())){
					opera.setString(i, "%"+officialDocOrgPojo.getCondTxt()+"%");
					++i;
				}
			}
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				totalSize = res.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return totalSize;
	}

	/* (non-Javadoc)
	 * @see com.union.core.doc.dao.OfficialDocDao#queryDocOrg(com.union.core.doc.pojo.OfficialDocOrgPojo, int, int)
	 */
	public List<OfficialDocOrg> queryDocOrg(OfficialDocOrgPojo officialDocOrgPojo, int start, int pageSize) {
		List<OfficialDocOrg> officialDocOrgList = new ArrayList<OfficialDocOrg>();
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select t.*,a.title,a.doc_no,a.send_time,(select b.organization_name from organization b,personnel c,sys_manager d where b.organization_id=c.organization_id and c.personnel_id=d.personnel_id and a.create_userid=d.manager_id limit 1) as organization_name," +
					" (select b.organization_name from organization b,personnel c,sys_manager d where b.organization_id=c.organization_id and c.personnel_id=d.personnel_id and t.sign_userid=d.manager_id limit 1) as sign_org_name," +
					" (select c.personnel_name from personnel c,sys_manager d where t.sign_userid=d.manager_id and c.personnel_id=d.personnel_id limit 1) sign_username"
					+ " from doc_org t,doc a " 
					+ " where t.doc_id = a.id and t.sign_status=? and t.org_id=? ";
			if(officialDocOrgPojo.getStartDate() != null){
				sql += " and a.send_time >= ? ";
			}
			if(officialDocOrgPojo.getEndDate() != null){
				sql += " and a.send_time <= ?";
			}
			if(!StringUtils.isNullOrEmpty(officialDocOrgPojo.getCondTxt())){
				if(IConstant.DOC_COND_TYPE_TIT.equals(officialDocOrgPojo.getCondType())){
					sql += " and a.title like ? ";
				}else if(IConstant.DOC_COND_TYPE_NO.equals(officialDocOrgPojo.getCondType())){
					sql += " and a.doc_no like ? ";
				}
			}
			sql += " order by id desc LIMIT ?,?";
			
			opera.prepStatment(sql);
			opera.setString(1, officialDocOrgPojo.getSign_status());
			opera.setLong(2, officialDocOrgPojo.getOrg_id());
			
			int i = 3;
			if(officialDocOrgPojo.getStartDate() != null){
				opera.setString(i, DateUtil.dateToStringFour(officialDocOrgPojo.getStartDate()));
				++i;
			}
			if(officialDocOrgPojo.getEndDate() != null){
				opera.setString(i, DateUtil.dateToStringFour(DateUtil.getEndDay(officialDocOrgPojo.getEndDate())));
				++i;
			}
			if(!StringUtils.isNullOrEmpty(officialDocOrgPojo.getCondTxt())){
				if(IConstant.DOC_COND_TYPE_TIT.equals(officialDocOrgPojo.getCondType())){
					opera.setString(i, "%"+officialDocOrgPojo.getCondTxt()+"%");
					++i;
				}else if(IConstant.DOC_COND_TYPE_NO.equals(officialDocOrgPojo.getCondType())){
					opera.setString(i, "%"+officialDocOrgPojo.getCondTxt()+"%");
					++i;
				}
			}
			
			opera.setInt(i, start);
			++i;
			opera.setInt(i, pageSize);
			++i;
			ResultSet res = opera.executeQuery();
			OfficialDocOrg officialDocOrg;
			while (null!=res && res.next()) {
				officialDocOrg = new OfficialDocOrg();
				officialDocOrg.setId(res.getLong("id"));
				officialDocOrg.setDoc_id(res.getLong("doc_id"));
				officialDocOrg.setOrg_id(res.getLong("org_id"));
				officialDocOrg.setSend_userid(res.getLong("send_userid"));
				officialDocOrg.setSend_time(res.getDate("send_time"));
				officialDocOrg.setSign_userid(res.getLong("sign_userid"));
				officialDocOrg.setSign_time(res.getDate("sign_time"));
				officialDocOrg.setSign_status(res.getString("sign_status"));
				officialDocOrg.getOfficialDoc().setTitle(res.getString("a.title"));
				officialDocOrg.getOfficialDoc().setDoc_no(res.getString("a.doc_no"));
				officialDocOrg.getOfficialDoc().setSend_time(res.getDate("a.send_time"));
				officialDocOrg.getOfficialDoc().getOrgManager().setOrganization_name(res.getString("organization_name"));
				officialDocOrg.setSignOrgName(res.getString("sign_org_name"));
				officialDocOrg.setSignUsername(res.getString("sign_username"));
				officialDocOrgList.add(officialDocOrg);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
	    return officialDocOrgList;	
	}

	public OfficialDocOrg queryOrg(long lid) {
		OfficialDocOrg officialDocOrg = null;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			
			String sql = "select * " 
				   + " from doc_org " 
				   + " where id=?";
			opera.prepStatment(sql);
			opera.setLong(1, lid);
			ResultSet res = opera.executeQuery();
			while(null!=res && res.next()){
				officialDocOrg = new OfficialDocOrg();
				officialDocOrg.setId(res.getLong("id"));
				officialDocOrg.setDoc_id(res.getLong("doc_id"));
				officialDocOrg.setOrg_id(res.getLong("org_id"));
				officialDocOrg.setSend_userid(res.getLong("send_userid"));
				officialDocOrg.setSend_time(res.getDate("send_time"));
				officialDocOrg.setSign_userid(res.getLong("sign_userid"));
				officialDocOrg.setSign_time(res.getDate("sign_time"));
				officialDocOrg.setSign_status(res.getString("sign_status"));
				break;
			}
		}catch(Exception e){
			e.printStackTrace();
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return officialDocOrg;
	}

	public int queryDocOrgForStatusCount(OfficialDocOrgPojo officialDocOrgPojo) {
		int totalSize = 0;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql= "select count(t.id) as totalSize "
						+ " from doc_org t left join doc a on t.doc_id = a.id left join organization b on t.org_id=b.organization_id " 
						+ " where t.doc_id = a.id ";
			if(!StringUtils.isNullOrEmpty(officialDocOrgPojo.getSign_status())){
				sql += " and t.sign_status = ? ";
			}
			if(officialDocOrgPojo.getStartDate() != null){
				sql += " and a.send_time >= ? ";
			}
			if(officialDocOrgPojo.getEndDate() != null){
				sql += " and a.send_time <= ?";
			}
			if(!StringUtils.isNullOrEmpty(officialDocOrgPojo.getCondTxt())){
				if(IConstant.SIGN_STATUS_ID.equals(officialDocOrgPojo.getCondType())){
					sql += " and t.doc_id = ? ";
				}else if(IConstant.SIGN_STATUS_TIT.equals(officialDocOrgPojo.getCondType())){
					sql += " and a.title like ? ";
				}else if(IConstant.SIGN_STATUS_DEPT.equals(officialDocOrgPojo.getCondType())){
					sql += " and b.organization_name like ? ";
				}
			}
			
			opera.prepStatment(sql);
			
			int i = 1;
			if(!StringUtils.isNullOrEmpty(officialDocOrgPojo.getSign_status())){
				opera.setString(i, officialDocOrgPojo.getSign_status());
				++i;
			}
			if(officialDocOrgPojo.getStartDate() != null){
				opera.setString(i, DateUtil.dateToStringFour(officialDocOrgPojo.getStartDate()));
				++i;
			}
			if(officialDocOrgPojo.getEndDate() != null){
				opera.setString(i, DateUtil.dateToStringFour(DateUtil.getEndDay(officialDocOrgPojo.getEndDate())));
				++i;
			}
			if(!StringUtils.isNullOrEmpty(officialDocOrgPojo.getCondTxt())){
				if(IConstant.SIGN_STATUS_ID.equals(officialDocOrgPojo.getCondType())){
					opera.setString(i, officialDocOrgPojo.getCondTxt());
					++i;
				}else if(IConstant.SIGN_STATUS_TIT.equals(officialDocOrgPojo.getCondType())){
					opera.setString(i, "%"+officialDocOrgPojo.getCondTxt()+"%");
					++i;
				}else if(IConstant.SIGN_STATUS_DEPT.equals(officialDocOrgPojo.getCondType())){
					opera.setString(i, "%"+officialDocOrgPojo.getCondTxt()+"%");
					++i;
				}
			}
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				totalSize = res.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return totalSize;
	}

	public List<OfficialDocOrg> queryDocOrgForStatus(OfficialDocOrgPojo officialDocOrgPojo, int start, int pageSize) {
		List<OfficialDocOrg> officialDocOrgList = new ArrayList<OfficialDocOrg>();
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "select t.*,a.id,a.title,a.doc_no,a.send_time,b.organization_name, " +
					" (select b.organization_name from organization b,personnel c,sys_manager d where b.organization_id=c.organization_id and c.personnel_id=d.personnel_id and a.create_userid=d.manager_id limit 1) as organization_name," +
					" (select b.organization_name from organization b,personnel c,sys_manager d where b.organization_id=c.organization_id and c.personnel_id=d.personnel_id and t.sign_userid=d.manager_id limit 1) as sign_org_name," +
					" (select c.personnel_name from personnel c,sys_manager d where t.sign_userid=d.manager_id and c.personnel_id=d.personnel_id limit 1) sign_username"
					+ " from doc_org t left join doc a on t.doc_id = a.id left join organization b on t.org_id=b.organization_id " 
					+ " where 1=1 ";
			if(!StringUtils.isNullOrEmpty(officialDocOrgPojo.getSign_status())){
				sql += " and t.sign_status = ? ";
			}
			if(officialDocOrgPojo.getStartDate() != null){
				sql += " and a.send_time >= ? ";
			}
			if(officialDocOrgPojo.getEndDate() != null){
				sql += " and a.send_time <= ?";
			}
			if(!StringUtils.isNullOrEmpty(officialDocOrgPojo.getCondTxt())){
				if(IConstant.SIGN_STATUS_ID.equals(officialDocOrgPojo.getCondType())){
					sql += " and t.doc_id = ? ";
				}else if(IConstant.SIGN_STATUS_TIT.equals(officialDocOrgPojo.getCondType())){
					sql += " and a.title like ? ";
				}else if(IConstant.SIGN_STATUS_DEPT.equals(officialDocOrgPojo.getCondType())){
					sql += " and b.organization_name like ? ";
				}
			}
			sql += " order by a.id desc,t.id asc LIMIT ?,?";
			
			opera.prepStatment(sql);
			
			int i = 1;
			if(!StringUtils.isNullOrEmpty(officialDocOrgPojo.getSign_status())){
				opera.setString(i, officialDocOrgPojo.getSign_status());
				++i;
			}
			if(officialDocOrgPojo.getDoc_id() > 0){
				opera.setLong(i, officialDocOrgPojo.getDoc_id());
				++i;
			}
			if(officialDocOrgPojo.getStartDate() != null){
				opera.setString(i, DateUtil.dateToStringFour(officialDocOrgPojo.getStartDate()));
				++i;
			}
			if(officialDocOrgPojo.getEndDate() != null){
				opera.setString(i, DateUtil.dateToStringFour(DateUtil.getEndDay(officialDocOrgPojo.getEndDate())));
				++i;
			}
			if(!StringUtils.isNullOrEmpty(officialDocOrgPojo.getCondTxt())){
				if(IConstant.SIGN_STATUS_ID.equals(officialDocOrgPojo.getCondType())){
					opera.setString(i, officialDocOrgPojo.getCondTxt());
					++i;
				}else if(IConstant.SIGN_STATUS_TIT.equals(officialDocOrgPojo.getCondType())){
					opera.setString(i, "%"+officialDocOrgPojo.getCondTxt()+"%");
					++i;
				}else if(IConstant.SIGN_STATUS_DEPT.equals(officialDocOrgPojo.getCondType())){
					opera.setString(i, "%"+officialDocOrgPojo.getCondTxt()+"%");
					++i;
				}
			}
			
			opera.setInt(i, start);
			++i;
			opera.setInt(i, pageSize);
			++i;
			ResultSet res = opera.executeQuery();
			OfficialDocOrg officialDocOrg;
			while (null!=res && res.next()) {
				officialDocOrg = new OfficialDocOrg();
				officialDocOrg.setId(res.getLong("id"));
				officialDocOrg.setDoc_id(res.getLong("doc_id"));
				officialDocOrg.setOrg_id(res.getLong("org_id"));
				officialDocOrg.setSend_userid(res.getLong("send_userid"));
				officialDocOrg.setSend_time(res.getDate("send_time"));
				officialDocOrg.setSign_userid(res.getLong("sign_userid"));
				officialDocOrg.setSign_time(res.getDate("sign_time"));
				officialDocOrg.setSign_status(res.getString("sign_status"));
				officialDocOrg.getOfficialDoc().setId(res.getLong("a.id"));
				officialDocOrg.getOfficialDoc().setTitle(res.getString("a.title"));
				officialDocOrg.getOfficialDoc().setDoc_no(res.getString("a.doc_no"));
				officialDocOrg.getOfficialDoc().setSend_time(res.getDate("a.send_time"));
				officialDocOrg.getOfficialDoc().getOrgManager().setOrganization_name(res.getString("organization_name"));
				officialDocOrg.setSignOrgName(res.getString("sign_org_name"));
				officialDocOrg.setSignUsername(res.getString("sign_username"));
				officialDocOrg.setOrg_name(res.getString("b.organization_name"));
				officialDocOrgList.add(officialDocOrg);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
	    return officialDocOrgList;	
	}

	public int querySealCount(SealSignature sealSignature) {
		int totalSize = 0;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql= "select count(t.id) as totalSize "
						+ " from seal_signature t left join organization a on t.belong_section = a.organization_id " 
						+ " where 1=1 ";
			if(!StringUtils.isNullOrEmpty(sealSignature.getBelong_section_name())){
				sql += " and a.organization_name like ? ";
			}
			if(!StringUtils.isNullOrEmpty(sealSignature.getSeal_type())){
				sql += " and t.seal_type = ?";
			}
			
			opera.prepStatment(sql);
			
			int i = 1;
			if(!StringUtils.isNullOrEmpty(sealSignature.getBelong_section_name())){
				opera.setString(i, "%" + sealSignature.getBelong_section_name() + "%");
				++i;
			}
			if(!StringUtils.isNullOrEmpty(sealSignature.getSeal_type())){
				opera.setString(i, sealSignature.getSeal_type());
				++i;
			}
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				totalSize = res.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return totalSize;
	}

	public List<SealSignature> querySeal(SealSignature sealSignature,
			int start, int pageSize) {
		List<SealSignature> sealSignatureList = new ArrayList<SealSignature>();
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql= "select t.*,a.organization_name "
				+ " from seal_signature t left join organization a on t.belong_section = a.organization_id " 
				+ " where 1=1 ";
			if(!StringUtils.isNullOrEmpty(sealSignature.getBelong_section_name())){
				sql += " and a.organization_name like ? ";
			}
			if(!StringUtils.isNullOrEmpty(sealSignature.getSeal_type())){
				sql += " and t.seal_type = ?";
			}
			sql += " order by t.id desc ";
			if(pageSize > 0){
				sql += " LIMIT ?,?";
			}
			
			opera.prepStatment(sql);
			
			int i = 1;
			if(!StringUtils.isNullOrEmpty(sealSignature.getBelong_section_name())){
				opera.setString(i, "%" + sealSignature.getBelong_section_name() + "%");
				++i;
			}
			if(!StringUtils.isNullOrEmpty(sealSignature.getSeal_type())){
				opera.setString(i, sealSignature.getSeal_type());
				++i;
			}
			
			if(pageSize > 0){
				opera.setInt(i, start);
				++i;
				opera.setInt(i, pageSize);
				++i;
			}
			ResultSet res = opera.executeQuery();
			SealSignature ss;
			while (null!=res && res.next()) {
				ss = new SealSignature();
				ss.setId(res.getLong("id"));
				ss.setTitle(res.getString("title"));
				ss.setFile_name(res.getString("file_name"));
				ss.setFile_path(res.getString("file_path"));
				ss.setFile_type(res.getString("file_type"));
				ss.setSeal_type(res.getString("seal_type"));
				ss.setBelong_section(res.getLong("belong_section"));
				ss.setEffect_flag(res.getBoolean("effect_flag"));
				ss.setCreate_userid(res.getLong("create_userid"));
				ss.setCreate_time(res.getDate("create_time"));
				ss.setBelong_section_name(res.getString("a.organization_name"));
				sealSignatureList.add(ss);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
	    return sealSignatureList;	
	}

	public SealSignature querySeal(long lid) {
		SealSignature sealSignature = null;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			
			String sql = "select * " 
				   + " from seal_signature " 
				   + " where id=?";
			opera.prepStatment(sql);
			opera.setLong(1, lid);
			ResultSet res = opera.executeQuery();
			while(null!=res && res.next()){
				sealSignature = new SealSignature();
				sealSignature.setId(res.getLong("id"));
				sealSignature.setTitle(res.getString("title"));
				sealSignature.setFile_name(res.getString("file_name"));
				sealSignature.setFile_path(res.getString("file_path"));
				sealSignature.setFile_type(res.getString("file_type"));
				sealSignature.setSeal_type(res.getString("seal_type"));
				sealSignature.setBelong_section(res.getLong("belong_section"));
				sealSignature.setEffect_flag(res.getBoolean("effect_flag"));
				sealSignature.setCreate_userid(res.getLong("create_userid"));
				sealSignature.setCreate_time(res.getDate("create_time"));
				break;
			}
		}catch(Exception e){
			e.printStackTrace();
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return sealSignature;
	}

	public boolean add(SealSignature sealSignature) {
    	boolean result = false;
    	try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "INSERT INTO `seal_signature`(`title`, `file_name`, `file_path`, `file_type`, `seal_type`, `belong_section`,`effect_flag`, `create_userid`, `create_time`)" +
					" VALUES(?,?,?,?,?,?,?,?,?)";
			opera.prepStatmentRtnKey(sql);
			opera.setString(1, sealSignature.getTitle());
			opera.setString(2, sealSignature.getFile_name());
			opera.setString(3, sealSignature.getFile_path());
			opera.setString(4, sealSignature.getFile_type());
			opera.setString(5, sealSignature.getSeal_type());
			opera.setLong(6, sealSignature.getBelong_section());
			opera.setInt(7, ComUtil.boolToInt(sealSignature.isEffect_flag()));
			opera.setLong(8, sealSignature.getCreate_userid());
			opera.setString(9, DateUtil.dateToStringFour(sealSignature.getCreate_time()));
			
			opera.execute();
			
			ResultSet rsKey = opera.getPres().getGeneratedKeys();
			rsKey.next();
			long key = rsKey.getLong(1);     //得到第一个键值
			rsKey.close();
			sealSignature.setId(key);
			result = opera.transcationCommit();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	public boolean edit(SealSignature sealSignature) {
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "UPDATE `seal_signature`SET `title` = ?,`file_name` = ?,`file_path` = ?,`file_type` = ?,`seal_type` = ?,`belong_section` = ?,`effect_flag` = ? WHERE `id` = ?;";
			
			opera.prepStatmentRtnKey(sql);
			opera.setString(1, sealSignature.getTitle());
			opera.setString(2, sealSignature.getFile_name());
			opera.setString(3, sealSignature.getFile_path());
			opera.setString(4, sealSignature.getFile_type());
			opera.setString(5, sealSignature.getSeal_type());
			opera.setLong(6, sealSignature.getBelong_section());
			opera.setInt(7, ComUtil.boolToInt(sealSignature.isEffect_flag()));
			opera.setLong(8, sealSignature.getId());

			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	public Boolean deleteSeal(long id) {
    	boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "delete from seal_signature "
					+ "where id=? ";
			opera.prepStatment(sql);
			opera.setLong(1, id);
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	public boolean add(List<DocAttachment> daList) {
    	boolean result = false;
    	try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "INSERT INTO `doc_attachment`(`doc_id`,`attach_title`,`attach_name`,`attach_old_name`,`attach_type`,`attach_path`,`effect_flag`,`create_userid`,`create_time`)" +
					" VALUES (?,?,?,?,?,?,?,?,?)";
			opera.prepStatmentRtnKey(sql);
			
			opera.getPres().clearBatch();
			opera.getPres().clearParameters();
			for(DocAttachment da : daList){
				int i = 1;
				opera.setLong(i, da.getDoc_id());
				++i;
				opera.setString(i, da.getAttach_title());
				++i;
				opera.setString(i, da.getAttach_name());
				++i;
				opera.setString(i, da.getAttach_old_name());
				++i;
				opera.setString(i, da.getAttach_type());
				++i;
				opera.setString(i, da.getAttach_path());
				++i;
				opera.setInt(i, ComUtil.boolToInt(da.isEffect_flag()));
				++i;
				opera.setLong(i, da.getCreate_userid());
				++i;
				opera.setString(i, DateUtil.dateToStringFour(da.getCreate_time()));
				opera.getPres().addBatch();
			}
			
			opera.getPres().executeBatch();
			
			ResultSet rsKey = opera.getPres().getGeneratedKeys();
			int i = 0;
			long key = 0;
			while(rsKey.next()){
				key = rsKey.getLong(1);     //得到第一个键值
				if(daList.get(i) != null){
					daList.get(i).setId(key);
				}
				++i;
			}
			rsKey.close();
			result = opera.transcationCommit();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	public List<DocAttachment> queryDocAttach(long doc_id) {
		List<DocAttachment> daList = new ArrayList<DocAttachment>();
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql= "select t.* "
				+ " from doc_attachment t " 
				+ " where t.doc_id=? and t.effect_flag=1 order by t.id asc ";
			
			opera.prepStatment(sql);
			
			opera.setLong(1, doc_id);
			
			ResultSet res = opera.executeQuery();
			DocAttachment da;
			while (null!=res && res.next()) {
				da = new DocAttachment();
				da.setId(res.getLong("id"));
				da.setAttach_title(res.getString("attach_title"));
				da.setAttach_name(res.getString("attach_name"));
				da.setAttach_old_name(res.getString("attach_old_name"));
				da.setAttach_type(res.getString("attach_type"));
				da.setAttach_path(res.getString("attach_path"));
				da.setEffect_flag(res.getBoolean("effect_flag"));
				da.setCreate_userid(res.getLong("create_userid"));
				da.setCreate_time(res.getDate("create_time"));
				daList.add(da);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
	    return daList;
	}

	public Boolean deleteDocAttach(long id) {
    	boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "delete from doc_attachment "
					+ "where id=? ";
			opera.prepStatment(sql);
			opera.setLong(1, id);
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	public DocAttachment queryDAById(long id) {
		DocAttachment da = null;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			
			String sql = "select * " 
				   + " from doc_attachment " 
				   + " where id=?";
			opera.prepStatment(sql);
			opera.setLong(1, id);
			ResultSet res = opera.executeQuery();
			while(null!=res && res.next()){
				da = new DocAttachment();
				da.setId(res.getLong("id"));
				da.setAttach_title(res.getString("attach_title"));
				da.setAttach_name(res.getString("attach_name"));
				da.setAttach_old_name(res.getString("attach_old_name"));
				da.setAttach_type(res.getString("attach_type"));
				da.setAttach_path(res.getString("attach_path"));
				da.setEffect_flag(res.getBoolean("effect_flag"));
				da.setCreate_userid(res.getLong("create_userid"));
				da.setCreate_time(res.getDate("create_time"));
				break;
			}
		}catch(Exception e){
			e.printStackTrace();
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return da;
	}

	public int queryTemplateCount(DocTemplate docTemplate) {
		int totalSize = 0;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql= "select count(t.id) as totalSize "
						+ " from doc_template t left join organization a on t.belong_section = a.organization_id " 
						+ " where 1=1 ";
			if(!StringUtils.isNullOrEmpty(docTemplate.getBelong_section_name())){
				sql += " and a.organization_name like ? ";
			}
			if(!StringUtils.isNullOrEmpty(docTemplate.getTemplate_type())){
				sql += " and t.template_type = ?";
			}
			
			opera.prepStatment(sql);
			
			int i = 1;
			if(!StringUtils.isNullOrEmpty(docTemplate.getBelong_section_name())){
				opera.setString(i, "%" + docTemplate.getBelong_section_name() + "%");
				++i;
			}
			if(!StringUtils.isNullOrEmpty(docTemplate.getTemplate_type())){
				opera.setString(i, docTemplate.getTemplate_type());
				++i;
			}
			ResultSet res = opera.executeQuery();
			if (res.next()) {
				totalSize = res.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return totalSize;
	}

	public List<DocTemplate> queryTemplate(DocTemplate docTemplate,
			int start, int pageSize) {
		List<DocTemplate> docTemplateList = new ArrayList<DocTemplate>();
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql= "select t.*,a.organization_name "
				+ " from doc_template t left join organization a on t.belong_section = a.organization_id " 
				+ " where 1=1 ";
			if(!StringUtils.isNullOrEmpty(docTemplate.getBelong_section_name())){
				sql += " and a.organization_name like ? ";
			}
			if(!StringUtils.isNullOrEmpty(docTemplate.getTemplate_type())){
				sql += " and t.template_type = ?";
			}
			sql += " order by t.id desc ";
			if(pageSize > 0){
				sql += " LIMIT ?,?";
			}
			
			opera.prepStatment(sql);
			
			int i = 1;
			if(!StringUtils.isNullOrEmpty(docTemplate.getBelong_section_name())){
				opera.setString(i, "%" + docTemplate.getBelong_section_name() + "%");
				++i;
			}
			if(!StringUtils.isNullOrEmpty(docTemplate.getTemplate_type())){
				opera.setString(i, docTemplate.getTemplate_type());
				++i;
			}
			
			if(pageSize > 0){
				opera.setInt(i, start);
				++i;
				opera.setInt(i, pageSize);
				++i;
			}
			ResultSet res = opera.executeQuery();
			DocTemplate dt;
			while (null!=res && res.next()) {
				dt = new DocTemplate();
				dt.setId(res.getLong("id"));
				dt.setTitle(res.getString("title"));
				dt.setFile_name(res.getString("file_name"));
				dt.setFile_path(res.getString("file_path"));
				dt.setFile_type(res.getString("file_type"));
				dt.setTemplate_type(res.getString("template_type"));
				dt.setBelong_section(res.getLong("belong_section"));
				dt.setEffect_flag(res.getBoolean("effect_flag"));
				dt.setCreate_userid(res.getLong("create_userid"));
				dt.setCreate_time(res.getDate("create_time"));
				dt.setBelong_section_name(res.getString("a.organization_name"));
				docTemplateList.add(dt);
			}
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
	    return docTemplateList;	
	}

	public DocTemplate queryTemplate(long lid) {
		DocTemplate docTemplate = null;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			
			String sql = "select * " 
				   + " from doc_template " 
				   + " where id=?";
			opera.prepStatment(sql);
			opera.setLong(1, lid);
			ResultSet res = opera.executeQuery();
			while(null!=res && res.next()){
				docTemplate = new DocTemplate();
				docTemplate.setId(res.getLong("id"));
				docTemplate.setTitle(res.getString("title"));
				docTemplate.setFile_name(res.getString("file_name"));
				docTemplate.setFile_path(res.getString("file_path"));
				docTemplate.setFile_type(res.getString("file_type"));
				docTemplate.setTemplate_type(res.getString("template_type"));
				docTemplate.setBelong_section(res.getLong("belong_section"));
				docTemplate.setEffect_flag(res.getBoolean("effect_flag"));
				docTemplate.setCreate_userid(res.getLong("create_userid"));
				docTemplate.setCreate_time(res.getDate("create_time"));
				break;
			}
		}catch(Exception e){
			e.printStackTrace();
			log.error(e);
		}finally{
			opera.closePres();
			opera.closeConn();
		}
		return docTemplate;
	}

	public boolean add(DocTemplate docTemplate) {
    	boolean result = false;
    	try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "INSERT INTO `doc_template`(`title`, `file_name`, `file_path`, `file_type`, `template_type`, `belong_section`,`effect_flag`, `create_userid`, `create_time`)" +
					" VALUES(?,?,?,?,?,?,?,?,?)";
			opera.prepStatmentRtnKey(sql);
			opera.setString(1, docTemplate.getTitle());
			opera.setString(2, docTemplate.getFile_name());
			opera.setString(3, docTemplate.getFile_path());
			opera.setString(4, docTemplate.getFile_type());
			opera.setString(5, docTemplate.getTemplate_type());
			opera.setLong(6, docTemplate.getBelong_section());
			opera.setInt(7, ComUtil.boolToInt(docTemplate.isEffect_flag()));
			opera.setLong(8, docTemplate.getCreate_userid());
			opera.setString(9, DateUtil.dateToStringFour(docTemplate.getCreate_time()));
			
			opera.execute();
			
			ResultSet rsKey = opera.getPres().getGeneratedKeys();
			rsKey.next();
			long key = rsKey.getLong(1);     //得到第一个键值
			rsKey.close();
			docTemplate.setId(key);
			result = opera.transcationCommit();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	public boolean edit(DocTemplate docTemplate) {
		boolean result = false;
		try{
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "UPDATE `doc_template`SET `title` = ?,`file_name` = ?,`file_path` = ?,`file_type` = ?,`template_type` = ?,`belong_section` = ?,`effect_flag` = ? WHERE `id` = ?;";
			
			opera.prepStatmentRtnKey(sql);
			opera.setString(1, docTemplate.getTitle());
			opera.setString(2, docTemplate.getFile_name());
			opera.setString(3, docTemplate.getFile_path());
			opera.setString(4, docTemplate.getFile_type());
			opera.setString(5, docTemplate.getTemplate_type());
			opera.setLong(6, docTemplate.getBelong_section());
			opera.setInt(7, ComUtil.boolToInt(docTemplate.isEffect_flag()));
			opera.setLong(8, docTemplate.getId());

			opera.executeUpdate();
			result = opera.transcationCommit();
		} catch (Exception e) {
			e.printStackTrace();
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}

	public Boolean deleteTemplate(long id) {
    	boolean result = false;
		try {
			opera = new DBOperation();
			opera.transcationBegin();
			String sql = "delete from doc_template "
					+ "where id=? ";
			opera.prepStatment(sql);
			opera.setLong(1, id);
			opera.execute();
			result = opera.transcationCommit();
		} catch (Exception e) {
			log.error(e);
		} finally {
			opera.closePres();
			opera.closeConn();
		}
		return result;
	}
}